package com.zhou.common.template.project.service.impl;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerDefaultImpl;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.google.common.collect.Maps;
import com.zhou.base.components.bs.common.exception.base.BaseExceptionBuilder;
import com.zhou.base.components.bs.common.util.DownloaFileUtil;
import com.zhou.common.template.project.excel.handler.TestDictAddressListHandlerImpl;
import com.zhou.common.template.project.excel.vo.ExportAddressListUserInfoVo;
import com.zhou.common.template.project.excel.vo.ExportNormalUserInfoVo;
import com.zhou.common.template.project.service.ExcelService;
import com.zhou.common.template.project.util.ExcelCascadeUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicInteger;

import static com.zhou.common.template.project.constant.CommonUsedConstant.ZERO;

/**
 * @author xianfeng
 * @date 2023/12/2 10:27
 * @description:
 */
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {

    private static final String SHEET_HIDDEN_MAP_NAME = "SHEET_MAP_HIDDEN_DEPT_ID";

    private static final String SELECT = "_下拉框";


    @Override
    public void downloadWithAddressList(HttpServletResponse response) {
        Map<String, String> rankMaps = Maps.newLinkedHashMap();
        for (int i = 0; i < 40; i++) {
            rankMaps.put("rank" + i, "职级" + i);
        }
        Map<String, String> orgMaps = Maps.newLinkedHashMap();
        for (int i = 0; i < 200; i++) {
            orgMaps.put("org" + i, "部门" + i);
        }
        //多级的map
        List<String> list = Lists.newArrayList();
        Map<String, List<String>> mapLevels = Maps.newLinkedHashMap();
        for (int i = 0; i < 200; i++) {
            List<String> subList = Lists.newArrayList();
            for (int j = 0; j < 200; j++) {
                subList.add("第一列" + i + "-第二列" + j);
            }
            list.add("第一列" + i);
            mapLevels.put("第一列" + i, subList);
        }
        ExportParams params = new ExportParams();
        params.setIndexName("序号");
        params.setAddIndex(true);
        params.setSheetName("sheet1");
        params.setType(ExcelType.XSSF);
        params.setStyle(ExcelExportStylerDefaultImpl.class);
        params.setDictHandler(new TestDictAddressListHandlerImpl(rankMaps, orgMaps));
        Workbook workbook = ExcelExportUtil.exportExcel(params, ExportAddressListUserInfoVo.class, Lists.newArrayList());
        ExcelCascadeUtils.export(workbook, list, mapLevels, 2, 1, 2);
        DownloaFileUtil.downloadExcel(response, "下载带有下拉列表的模板", () -> {
            try {
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                //没有抛出时，一定不能忘记打印了
                throw BaseExceptionBuilder.with(e).message("下载文件失败");
            } finally {
                IoUtil.close(workbook);
            }
        });
    }

    @Override
    public void downloadWithDeptId(HttpServletResponse response) {
        //生成测试的部门id
        String deptId = StrUtil.uuid();

        ExportParams params = new ExportParams();
        params.setSheetName("sheet1");
        params.setType(ExcelType.XSSF);
        params.setStyle(ExcelExportStylerDefaultImpl.class);
        Workbook workbook = ExcelExportUtil.exportExcel(params, ExportNormalUserInfoVo.class, Lists.newArrayList());
        Sheet mapSheet = workbook.createSheet(SHEET_HIDDEN_MAP_NAME);
        workbook.setSheetHidden(workbook.getSheetIndex(mapSheet), true);// 设置sheet是否隐藏
        Row row = mapSheet.createRow(0);
        Cell cell1 = row.createCell(0);
        cell1.setCellValue(deptId);
        DownloaFileUtil.downloadExcel(response, "下载带有隐藏属性的模板", () -> {
            try {
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                throw BaseExceptionBuilder.with(e);
            } finally {
                IoUtil.close(workbook);
            }
        });
    }

    @Override
    public String importWithDeptId(MultipartFile file) {
        String deptId = getDeptIdByHiddenSheet(file);
        return deptId;
    }

    /**
     * hultool下载
     *
     * @param response
     */
    @Override
    public void downloadCustom(HttpServletResponse response) {
        //构造数据
        List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
        List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
        List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
        List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
        List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");
        List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);
        Map<String, List<String>> dictTypeMap = MapUtil.newHashMap();
        dictTypeMap.put("aa" + SELECT, CollUtil.newArrayList("aa1", "aa2", "aa3", "aa4", "aa5", "aa6"));
        dictTypeMap.put("bb" + SELECT, CollUtil.newArrayList("bb1", "bb2", "bb3", "bb4", "bb5", "bb6"));
        dictTypeMap.put("cc" + SELECT, CollUtil.newArrayList("cc1", "cc2", "cc3", "cc4", "cc5", "cc6"));
        ArrayList<String> objects = CollUtil.newArrayList();
        for (int i = 0; i < 100; i++) {
            objects.add("dd" + i);
        }
        dictTypeMap.put("dd" + SELECT, objects);

        ExcelWriter excelWriter = ExcelUtil.getWriter(true);
        excelWriter.renameSheet(ZERO, "测试标题");
//        excelWriter.merge(rows.get(ZERO).size() - 1, "测试标题");
        excelWriter.write(rows, true);
        if (dictTypeMap.size() > ZERO) {
            List<String> fieldNameList = rows.get(ZERO);
            AtomicInteger columnI = new AtomicInteger();
            AtomicInteger sheetI = new AtomicInteger();
            fieldNameList.forEach(fieldName -> {
                String fieldSelect = fieldName + SELECT;
                List<String> singleDictType = dictTypeMap.get(fieldSelect);
                if (Objects.isNull(singleDictType)) {
                    columnI.getAndIncrement();
                    return;
                }
                sheetI.getAndIncrement();
                // 导出字典数据到另一个 sheet 页
                excelWriter.setSheet(fieldSelect);
                excelWriter.write(singleDictType, true);

                DataValidationHelper validationHelper = excelWriter.getSheet().getDataValidationHelper();
                DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(fieldSelect + "!$A$1:$A$" + singleDictType.size());

                // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
                CellRangeAddressList addressList = new CellRangeAddressList(1, 100000, columnI.get(), columnI.get());
                XSSFDataValidation validation = (XSSFDataValidation) validationHelper.createValidation(constraint, addressList);
                //隐藏
                Workbook workbook = excelWriter.getWorkbook();
                workbook.setSheetHidden(sheetI.get(), true);
                excelWriter.getSheets().get(ZERO).addValidationData(validation);
                columnI.getAndIncrement();
            });
        }
        DownloaFileUtil.downloadExcel(response, "自定义下载模版", () -> {
            ServletOutputStream outputStream = null;
            try {
                outputStream = response.getOutputStream();
                excelWriter.flush(outputStream, true);
            } catch (IOException e) {
                throw BaseExceptionBuilder.with(e);
            } finally {
                IoUtil.close(outputStream);
                IoUtil.close(excelWriter);
            }
        });
    }

    private String getDeptIdByHiddenSheet(MultipartFile file) {
        String deptId = null;
        try {
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            Sheet sheetMap = workbook.getSheet(SHEET_HIDDEN_MAP_NAME);
            deptId = sheetMap.getRow(0).getCell(0).getStringCellValue();
        } catch (Exception e) {
            throw BaseExceptionBuilder.with(e).message("获取导入部门id失败");
        }
        return deptId;
    }
}
